Skip Headers

Oracle® OLAP Reference 10g Release 2 (10.2) Part Number B14350-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# Creating a Metadata Command Script

Each procedure in the `CWM2_OLAP_EXPORT` package accepts a parameter that identifies a metadata command script file. The contents of this script are the commands that build the metadata.

The commands in the command script file may be `CWM2` procedure calls, `CWM1` procedure calls, CREATE DIMENSION statements to re-create the dimension objects associated with `CWM1` dimensions, and CREATE VIEW statements to re-create the views when the metadata is mapped to views of the source dimension tables and fact tables.

You can create a metadata command script without creating an Export parameter file. However, if you run the script in a different database without importing or re-creating the source tables, the metadata will be invalid.

Example 13-1 shows the metadata command script for the GLOBAL.PRODUCT dimension. This dimension was created in Enterprise Manager, therefore it has an associated Oracle dimension object and its metadata was defined using the `CWM1` APIs.

Example 13-1 Metadata Command Script for GLOBAL.PRODUCT

The following command creates a metadata command script for the GLOBAL.PRODUCT dimension. It does not create an Export parameter file.

    >EXECUTE cwm2_olap_export.export_dimension
                 ('GLOBAL', 'PRODUCT', '/myscripts', 'GLOBALPROD_CMD_SCRIPT.SQL');

To re-create the metadata, transfer the `GLOBALPROD_CMD_SCRIPT.SQL` file to a directory that can be accessed by the database. In SQL*Plus, navigate to this directory and run the script with a command like the following.

    >@GLOBALPROD_CMD_SCRIPT.SQL

The contents of `GLOBALPROD_CMD_SCRIPT.SQL` are shown as follows.

    CREATE DIMENSION GLOBAL.PRODUCT
      LEVEL CLASS IS (GLOBAL.PRODUCT_DIM.CLASS_ID)
      LEVEL FAMILY IS (GLOBAL.PRODUCT_DIM.FAMILY_ID)
      LEVEL ITEM IS (GLOBAL.PRODUCT_DIM.ITEM_ID)
      LEVEL TOTAL_PRODUCT IS (GLOBAL.PRODUCT_DIM.TOTAL_PRODUCT_ID)
      HIERARCHY PRODUCT_ROLLUP
      ( ITEM CHILD OF
        FAMILY CHILD OF
        CLASS CHILD OF
        FAMILY CHILD OF
        CLASS CHILD OF
        TOTAL_PRODUCT
      )
      ATTRIBUTE CLASS DETERMINES
      ( CLASS_DSC
      )
      ATTRIBUTE FAMILY DETERMINES
      ( FAMILY_DSC
      )
      ATTRIBUTE ITEM DETERMINES
      ( ITEM_DSC
      )
      ATTRIBUTE TOTAL_PRODUCT DETERMINES
      ( TOTAL_PRODUCT_DSC
      )
      ATTRIBUTE ITEM DETERMINES
      ( ITEM_PACKAGE_ID
      )
      ATTRIBUTE CLASS DETERMINES
      ( CLASS_DSC
      )
      ATTRIBUTE FAMILY DETERMINES
      ( FAMILY_DSC
      )
      ATTRIBUTE ITEM DETERMINES
      ( ITEM_DSC
      )
      ATTRIBUTE TOTAL_PRODUCT DETERMINES
      ( TOTAL_PRODUCT_DSC
      )
      ;
    EXECUTE cwm_olap_dimension.Set_Description('GLOBAL', 'PRODUCT', '');
    EXECUTE cwm_olap_dimension.Set_Display_Name('GLOBAL', 'PRODUCT', 'Product');
    EXECUTE cwm_olap_dimension.Set_Plural_Name('GLOBAL', 'PRODUCT', 'PRODUCT');
    EXECUTE cwm_olap_dim_attribute.Create_Dimension_Attribute
               ('GLOBAL', 'PRODUCT', 'Long_Description', 'Long Description', '');
    EXECUTE cwm_olap_dim_attribute.Create_Dimension_Attribute
               ('GLOBAL', 'PRODUCT', 'Package', 'Package', '');
    EXECUTE cwm_olap_dim_attribute.Create_Dimension_Attribute
               ('GLOBAL', 'PRODUCT', 'Short_Description', 'Short Description', '');
    EXECUTE cwm_olap_hierarchy.Set_Description('GLOBAL', 'PRODUCT', 'PRODUCT_ROLLUP', '');
    EXECUTE cwm_olap_hierarchy.Set_Display_Name('GLOBAL', 'PRODUCT', 'PRODUCT_ROLLUP', 'Product Rollup');
    EXECUTE cwm_olap_dimension.Set_Default_Display_Hierarchy('GLOBAL', 'PRODUCT', 'PRODUCT_ROLLUP');
    EXECUTE cwm_olap_level.Set_Description('GLOBAL', 'PRODUCT', 'CLASS', '');
    EXECUTE cwm_olap_level.Set_Display_Name('GLOBAL', 'PRODUCT', 'CLASS', 'Class');
    EXECUTE cwm_olap_level.Set_Description('GLOBAL', 'PRODUCT', 'FAMILY', '');
    EXECUTE cwm_olap_level.Set_Display_Name('GLOBAL', 'PRODUCT', 'FAMILY', 'Family');
    EXECUTE cwm_olap_level.Set_Description('GLOBAL', 'PRODUCT', 'ITEM', '');
    EXECUTE cwm_olap_level.Set_Display_Name('GLOBAL', 'PRODUCT', 'ITEM', 'Item');
    EXECUTE cwm_olap_level.Set_Description('GLOBAL', 'PRODUCT', 'TOTAL_PRODUCT', '');
    EXECUTE cwm_olap_level.Set_Display_Name('GLOBAL', 'PRODUCT', 'TOTAL_PRODUCT', 'Total Product');
    EXECUTE cwm_olap_level_attribute.Set_Name('GLOBAL', 'PRODUCT', 'CLASS', 'CLASS_DSC', 'CLASS_DSC');
    EXECUTE cwm_olap_dim_attribute.Add_Level_Attribute
               ('GLOBAL','PRODUCT','Short_Description','CLASS','CLASS_DSC');
    EXECUTE cwm_olap_level_attribute.Set_Description('GLOBAL', 'PRODUCT', 'CLASS', 'CLASS_DSC', '');
    EXECUTE cwm_olap_level_attribute.Set_Display_Name('GLOBAL', 'PRODUCT', 'CLASS', 'CLASS_DSC', '');
    EXECUTE cwm_olap_level_attribute.Set_Name
               ('GLOBAL', 'PRODUCT', 'FAMILY', 'FAMILY_DSC', 'FAMILY_DSC');
    EXECUTE cwm_olap_dim_attribute.Add_Level_Attribute
               ('GLOBAL','PRODUCT','Short_Description','FAMILY','FAMILY_DSC');
    EXECUTE cwm_olap_level_attribute.Set_Description
               ('GLOBAL', 'PRODUCT', 'FAMILY', 'FAMILY_DSC', '');
    EXECUTE cwm_olap_level_attribute.Set_Display_Name
               ('GLOBAL', 'PRODUCT', 'FAMILY', 'FAMILY_DSC', '');
    EXECUTE cwm_olap_level_attribute.Set_Name('GLOBAL', 'PRODUCT', 'ITEM', 'ITEM_DSC', 'ITEM_DSC');
    EXECUTE cwm_olap_dim_attribute.Add_Level_Attribute('GLOBAL','PRODUCT','Short_Description','ITEM','ITEM_DSC');
    EXECUTE cwm_olap_level_attribute.Set_Description('GLOBAL', 'PRODUCT', 'ITEM', 'ITEM_DSC', '');
    EXECUTE cwm_olap_level_attribute.Set_Display_Name('GLOBAL', 'PRODUCT', 'ITEM', 'ITEM_DSC', '');
    EXECUTE cwm_olap_level_attribute.Set_Name('GLOBAL', 'PRODUCT', 'ITEM', 'ITEM_PACKAGE_ID', 'ITEM_PACKAGE_ID');
    EXECUTE cwm_olap_dim_attribute.Add_Level_Attribute('GLOBAL', 'PRODUCT', 'Package', 'ITEM', 'ITEM_PACKAGE_ID');
    EXECUTE cwm_olap_level_attribute.Set_Description('GLOBAL', 'PRODUCT', 'ITEM', 'ITEM_PACKAGE_ID', '');
    EXECUTE cwm_olap_level_attribute.Set_Display_Name('GLOBAL', 'PRODUCT', 'ITEM', 'ITEM_PACKAGE_ID', '');
    EXECUTE cwm_olap_level_attribute.Set_Name
              ('GLOBAL','PRODUCT','TOTAL_PRODUCT','TOTAL_PRODUCT_DSC','TOTAL_PRODUCT_DSC');
    EXECUTE cwm_olap_dim_attribute.Add_Level_Attribute
              ('GLOBAL','PRODUCT','Short_Description','TOTAL_PRODUCT','TOTAL_PRODUCT_DSC');
    EXECUTE cwm_olap_level_attribute.Set_Description
              ('GLOBAL','PRODUCT','TOTAL_PRODUCT', 'TOTAL_PRODUCT_DSC', '');
    EXECUTE cwm_olap_level_attribute.Set_Display_Name
               ('GLOBAL', 'PRODUCT','TOTAL_PRODUCT','TOTAL_PRODUCT_DSC','');